/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package stagingetl;

import java.io.BufferedReader;
import java.io.File;
import java.io.BufferedWriter;
import java.io.ByteArrayInputStream;
import java.io.FileInputStream;
import java.io.FileWriter;
import java.io.InputStream;
import java.io.InputStreamReader;
import java.util.Properties;
import java.util.List;
import java.util.LinkedList;
import java.util.LinkedHashMap;
import java.util.Hashtable;

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.DriverManager;
import java.sql.ResultSetMetaData;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Iterator;
import javax.sql.DataSource;
import javax.xml.bind.JAXBContext;
import javax.xml.bind.JAXBException;
import javax.xml.bind.Unmarshaller;

import org.logicalcobwebs.proxool.ConfigurationListenerIF;
import org.logicalcobwebs.proxool.ConnectionListenerIF;
import org.logicalcobwebs.proxool.ProxoolDataSource;
import org.logicalcobwebs.proxool.ProxoolException;
import org.logicalcobwebs.proxool.ProxoolFacade;

import org.apache.commons.dbcp.BasicDataSource;
import org.apache.commons.beanutils.RowSetDynaClass;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

/**
 *
 * @author joen
 */
public class DbPool {
    private static final Logger logger = LoggerFactory.getLogger(DbPool.class);
    private static Properties poolProperties;
    private static Properties targetProperties = new Properties();
    private static String poolAlias;
    private static String mapDir;
    private static List<String> filterList;
    private static LinkedList<String> importLists;
    private static Connection dbConnections;
    private static String targetType = "";
    
    public DbPool () { }
    
    public void setMapDir (String mapDir) {
        this.mapDir = mapDir;
    }
    
    public String getMapDir() {
        return this.mapDir;
    }
    
    public void createPool (DbConnections connObjects) throws Exception {
        Class.forName("org.logicalcobwebs.proxool.ProxoolDriver");
        poolProperties = new Properties();
        poolProperties.setProperty("proxool.maximum-connection-count", "1500");
        poolProperties.setProperty("proxool.maximum-active-time", "1000");
        poolProperties.setProperty("user", connObjects.getdbuser());
        poolProperties.setProperty("password", connObjects.getdbpassword());
        String dbUrls = connObjects.getalias().trim().toString() + ":" + connObjects.getdbdriver().toString().trim() + ":" + connObjects.getdburl().toString().trim();
        ProxoolFacade.registerConnectionPool(dbUrls, poolProperties);    
    }
    

    
    public String  getQuery(String queryFile) throws JAXBException {
        File file = new File(mapDir +  queryFile);
        JAXBContext queryContext = JAXBContext.newInstance(GetSql.class);
        Unmarshaller queryUnmarshaller = queryContext.createUnmarshaller();
        GetSql queryString = (GetSql) queryUnmarshaller.unmarshal(file);
        return queryString.getSelect();
    }
    
    public RowSetDynaClass runQueryReturnRecordSet (String poolAlias, String queryID) throws SQLException{
        RowSetDynaClass rsdc = null;
        ResultSet resSets = null;
        String queryString = "";
        PreparedStatement query = null;
        
        try {
            queryString = getQuery (queryID);
        } catch (Exception eX) {
            logger.error (eX.getMessage());
        }
        if (queryString.trim().length() != 0) {
            try {
                dbConnections = DriverManager.getConnection(poolAlias);
                query = dbConnections.prepareStatement(queryString);
                resSets = query.executeQuery();                                
                 rsdc = new RowSetDynaClass(resSets);
                dbConnections.close();
            } catch (SQLException sqEX) {
                dbConnections.close();
                logger.error (sqEX.getMessage());
            }
        }
        return rsdc;
    }
    
    public ArrayList getTypeStructure(String poolAlias, String queryID) throws Exception {
        PreparedStatement query = null;
        ResultSet rSet = null;
        ResultSetMetaData rsetMD = null;
        ArrayList typeStructure = new ArrayList();
        String queryString = getQuery(queryID);
        
        try {
            dbConnections = DriverManager.getConnection(poolAlias);
            query = dbConnections.prepareStatement(queryString);
            rSet = query.executeQuery();
            rsetMD = rSet.getMetaData();
            for (int i=1; i<rsetMD.getColumnCount();i++) {
                typeStructure.add(rsetMD.getColumnTypeName(i));
            }
            dbConnections.close();
        } catch (SQLException sqX) {
            dbConnections.close();
            logger.error (sqX.getMessage());
        }
        
        return typeStructure;
    }
    
    public boolean isHoliday(String poolAlias, String queryID) throws Exception {
        PreparedStatement query = null;
        ResultSet rSet = null;
        String queryString = getQuery(queryID);
        String strHldy = "";
        boolean isHldy = false;
        
        try {
            dbConnections = DriverManager.getConnection(poolAlias);
            query = dbConnections.prepareStatement(queryString);
            rSet = query.executeQuery();
            while (rSet.next()) {
                strHldy = rSet.getString("hldy_dstn_cd");
            }
            dbConnections.close();
        } catch (Exception neX) {
            dbConnections.close();
            logger.error (neX.getCause().toString());
        }
        isHldy = strHldy.equalsIgnoreCase("0")?false:true;
        return isHldy;
    }
    
    public String cekProjobDate(String poolAlias, String queryID) throws Exception {
        PreparedStatement query = null;
        ResultSet rSet = null;
        String queryString = getQuery(queryID);
        String strResult = "";
        
        dbConnections = DriverManager.getConnection(poolAlias);
        query = dbConnections.prepareStatement(queryString);
        rSet = query.executeQuery();
        while (rSet.next()) {
            strResult = rSet.getString("lastdate");
        }
        
        dbConnections.close();
        if (rSet != null)  {
            rSet.close();
        }
        return strResult;   
    }
    
    public void runQuery(String pool, String queryString) throws Exception {
        PreparedStatement query = null;
        // System.out.println("lll : " + pool + " - " + queryString);
        dbConnections = DriverManager.getConnection(pool);
        query = dbConnections.prepareStatement(queryString);
        query.execute();
        dbConnections.close();
    }
    
    public String runSync(List<String> colLists, List<String> typeLists, List<String> valLists, String targetPool, String syncMode, String syncTable) {
        int i = 0;
        String queryString = "";
        if (syncMode.equalsIgnoreCase("truncate")) {
            StringBuffer colsBuffer = new StringBuffer();
            Iterator colsIterator = colLists.iterator();
            while (colsIterator.hasNext()) {
               colsBuffer.append(colsIterator.next().toString());   
               if (i<colLists.size() - 1) {
                   colsBuffer.append(", ");
               }
               i+=1;
            }

            i=0;
            StringBuffer typeBuffer = new StringBuffer();
            Iterator typeIterator = typeLists.iterator();
            while (typeIterator.hasNext()) {
                if (typeIterator.next().toString().equalsIgnoreCase("decimal")) {
                    typeBuffer.append("%s");
                } else {
                    typeBuffer.append("'%s'");
                }
                if (i<typeLists.size() - 1) {
                    typeBuffer.append(", ");
                }
                i+= 1;
            }

            queryString = String.format("insert into " +  syncTable.toLowerCase() + " (" + colsBuffer.toString() + " ) values ( " + typeBuffer.toString() + ");", valLists.toArray());
           
            colsBuffer.delete(0, colsBuffer.length());
            typeBuffer.delete(0, typeBuffer.length());
        }
        return queryString;
    }
    
    public ArrayList  getStructure (String poolAlias, String queryID) throws Exception {
        PreparedStatement query = null;
        ResultSet rSet = null;
        ResultSetMetaData rSetMD = null;
        String queryString = "";
        ArrayList structureMap = new ArrayList();
        
        queryString = getQuery(queryID);
        
        try {
            dbConnections = DriverManager.getConnection(poolAlias);
            query = dbConnections.prepareStatement(queryString);
            rSet = query.executeQuery();
            rSetMD = rSet.getMetaData();
            int cCount = 1;
            while ( cCount<=rSetMD.getColumnCount() ) {
                structureMap.add(rSetMD.getColumnName(cCount));
                cCount += 1;
            }
            dbConnections.close();
            
        } catch (SQLException sqX) {
            dbConnections.close();
            logger.error(sqX.getMessage());
        }
        return structureMap;
    }
    
    
    /*
     *  bagian ini khusus untuk database server MS-SQL karena jdbc driver MS-SQL tidak dapat menggunakan 
     */
    public void setProperties (DbConnections connObjects) throws Exception {
        if (!targetProperties.isEmpty()) {
            targetProperties.clear();
        }
        targetType = connObjects.getdbtype().toString();
        targetProperties.put("dbDriver", connObjects.getdbdriver());
        targetProperties.put("dbUrl", connObjects.getdburl());
    }
    
    private Connection getTConnection() throws SQLException, ClassNotFoundException {
        Class.forName(targetProperties.getProperty("dbDriver").toString());
        Connection tgConn = DriverManager.getConnection(targetProperties.getProperty("dbUrl").trim());
        return tgConn;
    }
    
    public void runMSquery(String queryString) throws ClassNotFoundException, SQLException {
        // Class.forName(targetProperties.getProperty("dbDriver").toString());
        // Connection tgConn = DriverManager.getConnection(targetProperties.getProperty("dbUrl").trim());
        // System.out.println(queryString);
        // PreparedStatement stmt = tgConn.prepareStatement(queryString);
        // stmt.execute();
        // tgConn.close();
    }
    
    public void runImports(String fileName) throws Exception {
        System.out.println("Start importing " + fileName);
        Connection targetDBConnection = null;
        Statement stmt = null;
        int insertQuery = 0;
        // String sqlString = "insert into st_stock_recon (dt, stock_id, sccm_cd, acno, clientid, clientsid, account_name, customer_no, onhand, buy, sell, ca, balance ) values ( '20140317', 'ADRO', 'DX', '00101000001', 'SW4546', 'IDD221216347900', 'Yayan Maryana Wirakusumah', '000000001', 7500.00, 0.00, 0.00, 0.00, 7500.00)";
        String sqlString = "";
        try {
            targetDBConnection = getTConnection();
            BufferedReader breader = new BufferedReader(new InputStreamReader(new FileInputStream(fileName)));
            stmt = targetDBConnection.createStatement();
            while ((sqlString = breader.readLine())!= null) {
                // System.out.println("ll : " + sqlString);
                insertQuery = stmt.executeUpdate(sqlString);
            }
        } catch (SQLException sqlex) {
            System.out.println(sqlex.getCause().toString());
        } catch (ClassNotFoundException cnfex) {
            System.out.println(cnfex.getCause().toString());
        }
        
        if (targetDBConnection!=null) {
            targetDBConnection.close();
        }
        System.out.println("Done Importing " + fileName);
    }
       
    
}
